﻿Imports System.IO
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.UserModel

Public Class frmPrint

    Dim m As New List(Of Model)
    Dim o As New List(Of Model)
    Dim errorrow As Int32
    Private Sub cbGrade_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'cmGrade.Items.Add("高一")
        'cmGrade.Items.Add("高二")
        'cmGrade.Items.Add("高三")
        'Dim i As Integer
        'For i = 1 To 12
        '    cbMonth.Items.Add(i)
        'Next
        CBkaochang.SelectedIndex = 2
    End Sub

    Private Sub btnExpt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExpt.Click
        Try
            Dim OpenFileDialog As New OpenFileDialog
            OpenFileDialog.Filter = "Excel文件|*.xls"
            If OpenFileDialog.ShowDialog() = System.Windows.Forms.DialogResult.Cancel Then
                Return
            End If
            Dim fName As String = OpenFileDialog.FileName
            Using file As New FileStream(fName, FileMode.Open, FileAccess.Read)
                Dim workbook As New HSSFWorkbook(file)
                Dim sheet As HSSFSheet = workbook.GetSheetAt(2)
                Dim headerRow As HSSFRow = sheet.GetRow(0)
                '//一行最后一个方格的编号 即总的列数
                Dim cellCount As Integer = headerRow.LastCellNum
                '//for (int i = headerRow.FirstCellNum i < cellCount i++)
                '    //
                '    //    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue)
                '    //    table.Columns.Add(column)
                '    //
                '    //最后一列的标号  即总的行数
                m = New List(Of Model)
                Dim rowCount As Integer = sheet.LastRowNum
                Dim i As Integer
                For i = (sheet.FirstRowNum + 1) To sheet.LastRowNum
                    errorrow = i
                    Dim row As IRow = sheet.GetRow(i)
                    Dim n As New Model
                    n.班级 = row.GetCell(row.FirstCellNum + 0).NumericCellValue.ToString()
                    n.姓名 = row.GetCell(row.FirstCellNum + 1).StringCellValue
                    n.智学号 = row.GetCell(row.FirstCellNum + 2).NumericCellValue.ToString()
                    n.科目1 = row.GetCell(row.FirstCellNum + 3).StringCellValue
                    n.考场1 = row.GetCell(row.FirstCellNum + 4).StringCellValue
                    n.座位号1 = row.GetCell(row.FirstCellNum + 5).NumericCellValue.ToString()
                    n.科目2 = ""
                    n.考场2 = ""
                    n.座位号2 = ""
                    n.科目3 = ""
                    n.考场3 = ""
                    n.座位号3 = ""
                    n.科目4 = ""
                    n.考场4 = ""
                    n.座位号4 = ""
                    n.科目5 = ""
                    n.考场5 = ""
                    n.座位号5 = ""
                    n.科目6 = ""
                    n.考场6 = ""
                    n.座位号6 = ""
                    If CBkaochang.Text >= "2" Then
                        n.科目2 = row.GetCell(row.FirstCellNum + 6).StringCellValue
                        n.考场2 = row.GetCell(row.FirstCellNum + 7).StringCellValue
                        n.座位号2 = row.GetCell(row.FirstCellNum + 8).NumericCellValue.ToString()
                    End If
                    If CBkaochang.Text >= "3" Then
                        n.科目3 = row.GetCell(row.FirstCellNum + 9).StringCellValue
                        n.考场3 = row.GetCell(row.FirstCellNum + 10).StringCellValue
                        n.座位号3 = row.GetCell(row.FirstCellNum + 11).NumericCellValue.ToString()
                    End If
                    If CBkaochang.Text >= "4" Then
                        n.科目4 = row.GetCell(row.FirstCellNum + 12).StringCellValue
                        n.考场4 = row.GetCell(row.FirstCellNum + 13).StringCellValue
                        n.座位号4 = row.GetCell(row.FirstCellNum + 14).NumericCellValue.ToString()
                    End If
                    If CBkaochang.Text >= "5" Then
                        n.科目5 = row.GetCell(row.FirstCellNum + 15).StringCellValue
                        n.考场5 = row.GetCell(row.FirstCellNum + 16).StringCellValue
                        n.座位号5 = row.GetCell(row.FirstCellNum + 17).NumericCellValue.ToString()
                    End If
                    If CBkaochang.Text = "6" Then
                        n.科目6 = row.GetCell(row.FirstCellNum + 18).StringCellValue
                        n.考场6 = row.GetCell(row.FirstCellNum + 19).StringCellValue
                        n.座位号6 = row.GetCell(row.FirstCellNum + 20).NumericCellValue.ToString()
                    End If
                    m.Add(n)
                Next
            End Using
        Catch a As Exception
            FormatDataGrid(dgStu)
            dgStu.ReadOnly = False
            dgStu.DataSource = m
            dgStu.Columns("班级").Width = 60
            dgStu.Columns("姓名").Width = 60
            dgStu.Columns("智学号").Width = 80
            dgStu.Columns("科目1").Width = 100
            dgStu.Columns("考场1").Width = 60
            dgStu.Columns("座位号1").Width = 80
            dgStu.Columns("科目2").Width = 100
            dgStu.Columns("考场2").Width = 60
            dgStu.Columns("座位号2").Width = 80
            dgStu.Columns("科目3").Width = 100
            dgStu.Columns("考场3").Width = 60
            dgStu.Columns("座位号3").Width = 80
            dgStu.Columns("科目4").Width = 100
            dgStu.Columns("考场4").Width = 60
            dgStu.Columns("座位号4").Width = 80
            dgStu.Columns("科目5").Width = 100
            dgStu.Columns("考场5").Width = 60
            dgStu.Columns("座位号5").Width = 80
            dgStu.Columns("科目6").Width = 100
            dgStu.Columns("考场6").Width = 60
            dgStu.Columns("座位号6").Width = 80
            'MsgBox("导入失败，第" + errorrow.ToString() + "行数据有问题，请检查。出错信息：" + a.Message.ToString())
        End Try
    End Sub

    Public Sub FormatDataGrid(ByVal DataGridView1 As DataGridView)
        '格式设置开始
        Dim col As DataGridViewColumn
        For Each col In DataGridView1.Columns
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        Next col
        DataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter       '列表头居中
        DataGridView1.AllowUserToAddRows = False
        DataGridView1.RowHeadersVisible = False
        DataGridView1.AllowUserToResizeColumns = False
        DataGridView1.AllowUserToResizeRows = False
        DataGridView1.EditMode = DataGridViewEditMode.EditOnEnter
    End Sub

    Private Sub btnPrintOption_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrintOption.Click
        Dim f As New 票据套打
        f.ShowDialog()
    End Sub

    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        Dim PrinterName As String
        Dim PD As New PrintDialog
        If DialogResult.OK = PD.ShowDialog() Then
            PrinterName = PD.PrinterSettings.PrinterName
            Dim i As Integer '当前正在打印的序号
            Dim j As Integer '当前打印页中的序号
            Dim re As Integer = 0 '当前打印页内容在m中的起始序号
            For i = 0 To m.Count - 1
                j = (i - re + 1) Mod 13
                If (o.Count > 0) Then
                    If (o(j - 2).班级 = m(i).班级) Then
                        o.Add(m(i))
                        If j = 12 Then
                            PrintDocument1.PrinterSettings = PD.PrinterSettings
                            PrintDocument1.Print()
                            o.Clear()
                            re = i + 1
                        End If
                    Else
                        PrintDocument1.PrinterSettings = PD.PrinterSettings
                        PrintDocument1.Print()
                        o.Clear()
                        re = i
                        o.Add(m(i))
                    End If
                Else
                    o.Add(m(i))
                End If
                If (i = m.Count - 1) Then
                    PrintDocument1.PrinterSettings = PD.PrinterSettings
                    PrintDocument1.Print()
                    o.Clear()
                End If
            Next
        End If
    End Sub

    Private Sub PrintDocument1_PrintPage(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDocument1.PrintPage
        Dim d As New Dictionary(Of String, DataModel)
        Dim dm As New List(Of DataModel)
        dm = New DataDAL().GetAll()
        For Each n In dm
            d(n.项目名称) = n
        Next
        'd("标题").项目内容 = cmGrade.Text + "年级" + cbMonth.Text + "月考桌贴"
        Dim i As Integer = 0
        Dim j As Integer = 0
        Dim k As Integer = 0
        Dim x As New Model
        For Each x In o
            d("班级").项目内容 = x.班级
            d("姓名").项目内容 = x.姓名
            d("智学号").项目内容 = x.智学号
            d("科目1").项目内容 = x.科目1
            d("考场1").项目内容 = x.考场1
            d("座位号1").项目内容 = x.座位号1
            d("科目2").项目内容 = x.科目2
            d("考场2").项目内容 = x.考场2
            d("座位号2").项目内容 = x.座位号2
            d("科目3").项目内容 = x.科目3
            d("考场3").项目内容 = x.考场3
            d("座位号3").项目内容 = x.座位号3
            d("科目4").项目内容 = x.科目4
            d("考场4").项目内容 = x.考场4
            d("座位号4").项目内容 = x.座位号4
            d("科目5").项目内容 = x.科目5
            d("考场5").项目内容 = x.考场5
            d("座位号5").项目内容 = x.座位号5
            d("科目6").项目内容 = x.科目6
            d("考场6").项目内容 = x.考场6
            d("座位号6").项目内容 = x.座位号6
            j = i Mod 2
            k = Int(i / 2)
            For Each s In d.Keys
                Dim f As Font = New Font(d(s).字体名称, d(s).字体大小)
                e.Graphics.DrawString(d(s).项目内容, f, Brushes.Black, (d(s).左边距 + j * 10) * 39.45, (d(s).上边距 + k * 4.75) * 39.45)
            Next
            i = i + 1
        Next
    End Sub

    Private Sub CBkaochang_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CBkaochang.SelectedIndexChanged
        If CBkaochang.Text = 2 Or CBkaochang.Text = 1 Then
            HAYJS.ConnStringOle = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\两门.GBL;Jet OLEDB:Database Password=YJS88485820"
        ElseIf CBkaochang.Text = 3 Then
            HAYJS.ConnStringOle = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\三门.GBL;Jet OLEDB:Database Password=YJS88485820"
        ElseIf CBkaochang.Text = 4 Then
            HAYJS.ConnStringOle = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\四门.GBL;Jet OLEDB:Database Password=YJS88485820"
        ElseIf CBkaochang.Text = 5 Then
            HAYJS.ConnStringOle = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\五门.GBL;Jet OLEDB:Database Password=YJS88485820"
        ElseIf CBkaochang.Text = 6 Then
            HAYJS.ConnStringOle = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\六门.GBL;Jet OLEDB:Database Password=YJS88485820"
        End If
    End Sub
End Class